USE OnlineFloralShop

---------------------------------PRODUCTS-----------------------------------------------------
--Get Product by Category ID and paging
CREATE PROC GetProductByCategoryId
@CategoryId varchar(10),
@OrderBy nvarchar(20),
@PageIndex int,
@PageSize int
AS
	-- Create a variable @SQLStatement
	DECLARE @SQLStatement varchar(1000)
	-- Enter the dynamic SQL statement into the
	-- variable @SQLStatement
	SELECT @SQLStatement = '
	SET NOCOUNT ON;
      SELECT ROW_NUMBER() OVER
      (
            ORDER BY '+ @OrderBy +'
      )AS RowNumber,Products.ProductId,ProductName,Price,Status,[Images],Description,Details,Quantity    
     INTO #Results
     FROM [Products],[Cate_Products]
	 WHERE Products.ProductId = Cate_products.ProductId
		 AND Products.Status = 1
		 AND Cate_products.CategoryId = ''' + @CategoryId +	''' 
	
     SELECT * FROM #Results
     WHERE RowNumber BETWEEN '+convert(nvarchar(3),((@PageIndex - 1)*@PageSize + 1)) +' AND '+convert(nvarchar(3),((((@PageIndex -1) * @PageSize  + 1) + @PageSize) - 1))

    -- Execute the SQL statement
	EXEC(@SQLStatement)
GO
-------------------------------------------
--Count record of Products by CategoryId
CREATE PROC GetCountProductsByCategoryId
@CategoryId varchar(10)
AS
	SELECT COUNT(*) CountRows FROM Products,Cate_products
	WHERE Products.ProductId = Cate_products.ProductId
		 AND Products.Status = 1
		 AND Cate_products.CategoryId =  @CategoryId
GO


---------------------------------
--Get Product Detail by Product ID
CREATE PROC GetProductDetailByProductId
@ProductId varchar(10)
AS
	SELECT * FROM Products
	WHERE ProductId = @ProductId
GO	

---------------------------------
--Get All Category 
CREATE PROC GetAllCategories
AS
	SELECT * FROM Categories
GO	



---------------------------------
--Get All Category 
CREATE PROC GetCategoriesByCategoryId
@CategoryId varchar(10)
AS
	SELECT * FROM Categories
	WHERE CategoryId = @CategoryId
GO	
---------------------------------
--Get All Products 
CREATE PROC GetAllProducts
@OrderBy nvarchar(30)
AS
	DECLARE @SQLStatement varchar(255)
	-- Enter the dynamic SQL statement into the
	-- variable @SQLStatement
	SELECT @SQLStatement = 'SELECT TOP(10) * FROM Products WHERE Status = 1 ORDER BY '+ @OrderBy
	EXEC(@SQLStatement)
GO

--Get Reviews By ProductId - Paging
CREATE PROC GetReviewByProductId
@ProductId varchar(10),
@PageIndex INT = 1,
@PageSize INT = 10
AS
BEGIN  
	SET NOCOUNT ON;
      SELECT ROW_NUMBER() OVER
      (
            ORDER BY [DateAdded] DESC
      )AS RowNumber,*      
     INTO #Results
     FROM [Review]
	 WHERE ProductId = @ProductId AND Status = 1

     SELECT * FROM #Results
     WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
END
GO


-------------------------------------------------
--Get product relate
CREATE PROC GetProductsRelate
@ProductId varchar(10)
AS
	DECLARE @SQLStatement varchar(255),
			@ProductName nvarchar(20)
	SELECT @ProductName = ProductName FROM Products WHERE ProductId = @ProductId
	-- Enter the dynamic SQL statement into the
	-- variable @SQLStatement
	SELECT @SQLStatement = 'SELECT TOP(6) * FROM Products WHERE Status = 1 AND ProductName LIKE ''%'+ LEFT(@ProductName,4)+'%'''
	EXEC(@SQLStatement) 
GO


------------------------------------------------------
--GetCount(*) Review
CREATE PROC GetCountReviewByProductId
@ProductId varchar(10)
AS
	SELECT COUNT(*) AS CountReview FROM Review WHERE ProductId = @ProductId AND Status = 1

--//////////////////////////////////
--Add Comment (Review)
CREATE PROC AddReview
@ProductId varchar(10),
@Author nvarchar(30),
@Email nvarchar(50),
@Content nvarchar(300)
AS
	INSERT INTO Review(ProductId,Author,Email,[Content])
	VALUES(@ProductId,@Author,@Email,@Content)
GO




---------------------------------USER------------------------------------------------
--Get orders history by user ID
CREATE PROC GetOrdersByUserId
@UserId uniqueidentifier
AS
	SELECT * FROM [Order]
	WHERE UserId = @UserId
GO

	

---------------------------------ORDER-----------------------------------------------
--Get order details by Order ID
CREATE PROC GetOrderDetailsByOrderId
@OrderId nvarchar(10)
AS
	SELECT *,Quantity*Price AS Total FROM [OrderDetails]
	WHERE OrderId = @OrderId
GO


---------------------------------
--Get sum(Total) order details by Order ID
CREATE PROC GetOrderDetailsByOrderId
@OrderId nvarchar(10)
AS
	SELECT SUM(Quantity*Price) AS Total FROM [OrderDetails]
	WHERE OrderId = @OrderId
GO

